package hql;

import java.util.Arrays;
import java.util.List;

import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.boot.registry.StandardServiceRegistry;
import org.hibernate.boot.registry.StandardServiceRegistryBuilder;
import org.hibernate.cfg.Configuration;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import com.mysql.fabric.xmlrpc.base.Array;


public class HibernateTest1 {

	private SessionFactory sessionFactory;
	private Session session;
	private Transaction transaction;
	
	@Test
	public void testHQLUpdate(){
		String hql = "DELETE FROM Department d WHERE d.id = :id";
		session.createQuery(hql).setInteger("id", 21).executeUpdate();
	}
	
	@Test
	/**
	 * 	ʹ�ñ��� SQL ����
	 */
	public void testNativeSQL(){
		String sql = "INSERT INTO gg_department VALUES(?, ?)";
		Query query = session.createSQLQuery(sql);
		query.setInteger(0, 21).setString(1, "CCUT").executeUpdate();
	}
	
	@Test
	/**
	 * 	������������: ���ص� Department ����ļ���, ÿ�� Department �Ķ����  Employee �������Զ�����ʼ���� 
	 */
	public void testInnerJoinFetch(){
		String hql = "SELECT DISTINCT d FROM Department d INNER JOIN FETCH d.emps";
		Query query = session.createQuery(hql);
		List<Department> depts = query.list();
		System.out.println(depts.size());
		for(Department dept: depts){
			System.out.println(dept.getName() + "-" + dept.getEmps().size());
		}
	}
	
	@Test
	/**
	 * 	��������: ���������ļ�������������
	 */
	public void testLeftJoin(){
		String hql = "SELECT DISTINCT d FROM Department d LEFT JOIN d.emps";
		Query query = session.createQuery(hql);
		List<Department> result = query.list();
		for(Department dept: result){
			System.out.println(dept.getName());
			System.out.println(dept.getEmps().size());
		}
	}
	
	@Test
	/**
	 * 	������������: ���ص� Department ����ļ���, ÿ�� Department �Ķ����  Employee �������Զ�����ʼ���� 
	 */
	public void testLeftJoinFetch(){
		String hql = "SELECT DISTINCT d FROM Department d LEFT JOIN FETCH d.emps";
		Query query = session.createQuery(hql);
		List<Department> depts = query.list();
		System.out.println(depts.size());
		for(Department dept: depts){
			System.out.println(dept.getName() + "-" + dept.getEmps().size());
		}
	}
	
	@Test
	/**
	 * 	����ʹ�þۼ�����, �Լ� HAVING GROUP BY
	 */
	public void testGroupBy(){
		String hql = "SELECT min(e.salary), max(e.salary) FROM Employee e GROUP BY e.dept "
				+ "HAVING min(salary) > :minSal";
		Query query = session.createQuery(hql);
		List<Object[]> emps = query.setDouble("minSal", 5000).list();
		for(Object[] objects: emps){
			System.out.println(Arrays.asList(objects));
		}
	}
	
	@Test
	/**	
	 *  Ҳ������ hql ����а� email salary dept ����װ�� Employee ������, ��ѯ�Ľ���� Employee �� List
	 *  ��Ҫ�� Employee �����ṩ��Ӧ�Ĺ�����
	 */
	public void testFieldQuery2(){
		String hql = "SELECT new Employee(e.email, e.salary, e.dept) FROM Employee e WHERE e.dept = :dept";
		Query query = session.createQuery(hql);
		
		Department dept = new Department();
		dept.setId(11);
		List<Employee> result = query.setEntity("dept", dept).list();
		for(Employee emp: result){
			System.out.println(emp);
		}
	}	
	
	@Test
	/**
	 * 	ͶӰ��ѯ���Ĭ���� Object[] ����
	 */
	public void testFieldQuery(){
		String hql = "SELECT e.email, e.salary, e.dept FROM Employee e WHERE e.dept = :dept";
		Query query = session.createQuery(hql);
		
		Department dept = new Department();
		dept.setId(9);
		List<Object[]> result = query.setEntity("dept", dept).list();
		for(Object[] objects: result){
			System.out.println(Arrays.asList(objects));
		}
	}
	
	@Test
	public void testNamedQuery(){
		// ������ѯ: ���԰� HQL ���д�� hbm.xml �ļ���
		Query query = session.getNamedQuery("salaryEmps");
		List<Employee> emps = query.setDouble("minSal", 4000).setDouble("maxSal", 5000).list();
		System.out.println(emps.size());
	}
	
	@Test
	public void testPageQuery(){
		String hql = "FROM Employee";
		Query query = session.createQuery(hql);
		
		int pageNumber = 7;// ��ʾ�� 3 ҳ
		int pageSize = 4;// ÿҳ��ʾ 4 ����¼
		// 							��һ����¼�� 0 ��ʼ,								ÿҳ��ʾ 4 ����¼
		List<Employee> emps = query.setFirstResult((pageNumber-1) * pageSize).setMaxResults(pageSize).list();
		System.out.println(emps);
	}
	
	@Test
	public void testHQLNamedParameter(){
		// ������������, ����Ҳ������ʵ������
		String hql = "FROM Employee e WHERE e.salary > :sal AND e.dept = :dept ORDER BY e.salary";
		// ���� Query ����
		Query query = session.createQuery(hql);
		// �󶨲���
		Department dept = new Department();
		dept.setId(9);
		query.setDouble("sal", 6000).setEntity("dept", dept);
		// ִ�в�ѯ
		List<Employee> emps = query.list();
		System.out.println(emps.size());
	}
	
	@Test
	public void testHQLHelloworld(){
		// ����λ�ò���
		String hql = "FROM Employee e WHERE e.salary > ? AND e.email LIKE ?";
		// ���� Query ����
		Query query = session.createQuery(hql);
		// �󶨲���, ������׺
		query.setDouble(0, 6000).setString(1, "%a%");
		// ִ�в�ѯ
		List<Employee> emps = query.list();
		System.out.println(emps.size());
	}
	
	@Before
	public void init(){
		System.out.println("InitMethodInvoke");
		Configuration configuration = new Configuration().configure();
		StandardServiceRegistry serviceRegistry = new StandardServiceRegistryBuilder().configure().build();
		sessionFactory = configuration.buildSessionFactory(serviceRegistry);
		session = sessionFactory.openSession();
		transaction = session.beginTransaction();
	}
	
	@After
	public void destroy(){
		System.out.println("DestroyMethodInvoke");
		transaction.commit();
		session.close();
		sessionFactory.close();
	}
}
